from datetime import datetime

from sqlalchemy import delete, select, update, desc
from sqlalchemy import func
from sqlalchemy.ext.asyncio import AsyncSession

from module_stock.entity.do.transfer_do import FundTransfer
from module_stock.entity.vo.transfer_vo import TransferModel, TransferPageQueryModel
from utils.page_util import PageUtil


class TransferDao:
    """
    银证转账模块数据库操作层
    """

    @classmethod
    async def get_transfer_detail_by_id(cls, db: AsyncSession, id: int):
        """
        根据主键ID获取银证转账详细信息

        :param db: orm对象
        :param id: 主键ID
        :return: 银证转账信息对象
        """
        transfer_info = (
            (
                await db.execute(
                    select(FundTransfer)
                    .where(
                        FundTransfer.id == id
                    )
                )
            )
            .scalars()
            .first()
        )

        return transfer_info

    @classmethod
    async def get_transfer_list(cls, db: AsyncSession, query_object: TransferPageQueryModel, is_page: bool = False):
        """
        根据查询参数获取银证转账列表信息

        :param db: orm对象
        :param query_object: 查询参数对象
        :param is_page: 是否开启分页
        :return: 银证转账列表信息对象
        """
        if query_object.account_nos:
            query_object.account_no_list = [item.strip() for item in query_object.account_nos.split(',') if
                                            item.strip()]
        query = (
            select(FundTransfer)
            .where(
                FundTransfer.transfer_date == query_object.transfer_date if query_object.transfer_date else True,
                FundTransfer.operation == query_object.operation if query_object.operation else True,
                FundTransfer.account_no == query_object.account_no if query_object.account_no else True,
                FundTransfer.account_no.in_(query_object.account_no_list) if query_object.account_no_list else True,
            )
            .order_by(desc(FundTransfer.transfer_date))
            .distinct()
        )
        transfer_list = await PageUtil.paginate(db, query, query_object.page_num, query_object.page_size, is_page)

        return transfer_list

    @classmethod
    async def add_transfer_dao(cls, db: AsyncSession, transfer: TransferModel):
        """
        新增银证转账数据库操作

        :param db: orm对象
        :param transfer: 银证转账对象
        :return:
        """
        db_transfer = FundTransfer(**transfer.model_dump(exclude={}))
        db.add(db_transfer)
        await db.flush()

        return db_transfer

    @classmethod
    async def edit_transfer_dao(cls, db: AsyncSession, transfer: dict):
        """
        编辑银证转账数据库操作

        :param db: orm对象
        :param transfer: 需要更新的银证转账字典
        :return:
        """
        await db.execute(update(FundTransfer), [transfer])

    @classmethod
    async def delete_transfer_dao(cls, db: AsyncSession, transfer: TransferModel):
        """
        删除银证转账数据库操作

        :param db: orm对象
        :param transfer: 银证转账对象
        :return:
        """
        await db.execute(delete(FundTransfer).where(FundTransfer.id.in_([transfer.id])))

    @classmethod
    async def get_cumulative_transfer_amount(cls, db: AsyncSession, account_list: list, stat_date: str):
        """
        获取指定账户在指定日期之前的累计转账金额

        :param db: orm对象
        :param account_list: 账号列表
        :param stat_date: 统计截止日期
        :return: 累计转账金额
        """

        result = await db.execute(
            select(func.sum(FundTransfer.amount))
            .where(
                FundTransfer.transfer_date <= datetime.strptime(stat_date, '%Y-%m-%d'),
                FundTransfer.account_no.in_(account_list)
            )
        )
        amount = result.scalar()
        return amount if amount else 0

    @classmethod
    async def get_transfer_amount_by_account_nos(cls, db: AsyncSession, account_nos: list):
        """
        根据账户列表获取转账金额总和

        :param db: orm对象
        :param account_nos: 账户列表
        :return: 转账金额总和
        """
        if not account_nos:
            return 0

        result = await db.execute(
            select(func.sum(FundTransfer.amount))
            .where(FundTransfer.account_no.in_(account_nos))
        )
        amount = result.scalar()
        return amount if amount else 0
